﻿CREATE PROCEDURE [dbo].[proc_Project_GetList_Interface]
	(
		@SearchWay int,
		@StartDate varchar(10),
		@EndDate varchar(10),
		@Stext nvarchar(50),
		@ProductId int,
		@IsShowMove int,
		@UserName varchar(50),
		@IsFinish int,
		@NOE int,
		@KF varchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @CompanyId INT
	Select @CompanyId = CompanyId FROM Employee WHERE UserName = @UserName
	If @CompanyId Is Null
		Set @CompanyId = 0
	
	Declare @sql nvarchar(4000),@cWhere nvarchar(1000)
	
	Set @sql='Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
	;WITH list As(Select Top 4500 ROW_NUMBER() OVER '
	
	If @NOE = 100	--成单类型，0常规 1非企 2非常规
		Set @cWhere = 'Where '
	Else
		Set @cWhere = 'Where NOE_Flag=' + Convert(varchar(10),@NOE) + ' And'

	If @SearchWay = 0	--下发日期
		Begin
			Set @sql = @sql + '(ORDER BY IsFinish,CreateDate Desc,Id Desc) '
			Set @cWhere = @cWhere + ' CreateDate>=@sRq And CreateDate<=@eRq'
			If @IsFinish <> 2	--是否完成
				Set @cWhere = @cWhere + ' And IsFinish='+Convert(varchar(10),@IsFinish)
		End
	Else	--完结日期
		Begin
			Set @sql = @sql + '(ORDER BY FinishDate Desc,Id Desc) '
			Set @cWhere = @cWhere + ' IsFinish=1 And FinishDate>=@sRq And FinishDate<=@eRq'
		End
	
	If @ProductId > 0	--选定项目
		Set @cWhere = @cWhere + ' And ProductId=' + Convert(varchar(10),@ProductId)
		
	If @IsShowMove = 1	--含转移
		Set @cWhere = @cWhere + ' And (ExecPerson=''' + @UserName + ''' Or MoveUserId=''' + @UserName + ''') '
	Else
		Set @cWhere = @cWhere + ' And ExecPerson=''' + @UserName + ''' And CompanyId='+Convert(varchar(10),@CompanyId)+' And (MoveUserId IS NULL Or MoveCompanyId='+Convert(varchar(10),@CompanyId)+')'

	If @Stext <> ''
	Begin
		If @KF = '0'	--客户名称
			Set @cWhere = @cWhere + ' And CusName Like ''%' + @Stext + '%'''
		Else	--报备人
			Set @cWhere = @cWhere + ' And Salesman Like ''%' + @Stext + '%'''
	End
	
	Set @sql = @sql + 'As Row,
		P.Id,
		P.ProjectId,
		P.OrderId,
		P.CusId,  
		p.CusName,
		P.CostPrice,
		P.SalePrice,
		P.Description,
		P.ProductId,
		p.ProductName,
		p.TypeName,
		p.SupplierName,
		p.CompanyId,
		p.Salesman,
		p.SalesmanAccount,
		P.IsFinish,
		p.IsHedge,
		NOE_Flag,ExecPerson,MoveCompanyId,
		
		' 
		

	If @SearchWay = 0
		Set @sql = @sql + 'p.CreateDate,
		IsNull(p.FinishDate,''1910-01-01'') as FinishDate
		
		From Project p '
	Else
		Set @sql = @sql + 'IsNull(p.CreateDate,''1910-01-01'') AS FinishDate,
		IsNull(p.FinishDate,''1910-01-01'') as CreateDate
		
		From Project p '
	
	Set @sql = @sql + @cWhere
	Set @sql = @sql + ') Select *,
	(Select Count(0) From Project_Task Where ProjectId=list.ProjectId) As ChildCount,
	(Select Count(0) From list) As RecordCount,
	(select flag from [Product] where list.ProductId=Product.id) as flag From list
	Where Row Between ' + CONVERT(VARCHAR(10),@StartIndex) + ' And ' + Convert(VARCHAR(10),@EndIndex) + ' Order By Row'

	Exec(@sql)
End